﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Windows.Forms;
using System.Globalization;
using System.Linq;
using System.Text;
using System.IO;

using Npgsql;
//using SpectationClient.DataBaseDescription;
using SpectationClient.DataBaseDescription;
using SpectationClient.Stuff;

namespace SpectationClient.SQLCommandBuilder {
    public class InsertValues {
        private TableInfo ti;
        private Dictionary<String, Object> rowValue = new Dictionary<string, object>();
       
        public InsertValues(ref TableInfo ti) {
            this.ti = ti;
        }

        public Dictionary<String, Object> Values { get { return this.rowValue; } }


        public NpgsqlCommand getInsertCmd() {
            return this.getInsertCmd(false);
        }

        public NpgsqlCommand getInsertCmd(bool returnPrimaryKeyValues) { 
            if(rowValue.Count == 0) return null;

            NpgsqlCommand cmd = new NpgsqlCommand(
                String.Format("INSERT INTO {0} ({1}) VALUES ("
                , ti.SchemaTableName
                , TextHelper.combine(rowValue.Keys.ToArray(), "\"{0}\"", ",")));
            int cntParameters = 0;
            int cntColumns = 0;
            foreach(String column in rowValue.Keys) {
                //ColumnInfoGeometry gci = this.ti[column] as ColumnInfoGeometry;
                ColumnInfo ci = this.ti[column] as ColumnInfo;

                if(ci != null){
                    if(rowValue[column] is NpgsqlCommand) {
                        CommandBuilder.appendCmd(ref cmd, rowValue[column] as NpgsqlCommand);
                        cmd.CommandText += " ";
                    } else {
                        String parameterName = String.Format("@p{0}", cntParameters);
                        cmd.CommandText += parameterName;
                        cmd.Parameters.AddWithValue(parameterName, rowValue[column]);
                        cntParameters++;

                    }
                } else {
                    throw new Exception("unhandled ColumnInfo");
                }
                if(cntColumns < rowValue.Count - 1) cmd.CommandText += ",";

                cntColumns++;
                    
            }
            cmd.CommandText += ")";
            if(returnPrimaryKeyValues) {
                cmd.CommandText += String.Format(" RETURNING {0}"
                    , TextHelper.combine(this.ti.PrimaryKey.Names, "\"{0}\"", ","));
            }
            return cmd;
        }

        public bool IsEmpty { get { return rowValue.Count == 0; } }
        public TableInfo TableInfo { get { return this.ti; } }


        /// <summary>
        /// Checks whether a column is described by ColumnInfoGeometry.
        /// Throws an error if not and returns the columnSRID of the ColumnInfoGeometry
        /// </summary>
        /// <param name="column"></param>
        /// <returns>SRID of the ColumnInfoGeometry</returns>
        private long checkGeometryColumn(String column) {
            ColumnInfoGeometry gci = this.ti[column] as ColumnInfoGeometry;
            if(gci == null) {
                throw new Exception(String.Format("Column {0} is not a geometrySQL column.", column));
            }
            return gci.SRID;
        }

        /// <summary>
        /// Adds a coordinate described as Well-Known-Text String.
        /// </summary>
        /// <param name="column"></param>
        /// <param name="WKT"></param>
        /// <param name="srid"></param>
        public void AddCoordinate(String column, String WKT, long srid) {
            long columnSRID = this.checkGeometryColumn(column);
            String geometrySQL = String.Format("ST_GeometryFromText({0}, {1})", WKT, srid);
            if(srid != columnSRID) String.Format("ST_Transform({0},{1})", geometrySQL, columnSRID);
            rowValue.Add(column, new NpgsqlCommand(geometrySQL));
        }
        
        /// <summary>
        /// Adds a coordinate described as Well-Known-Binary Byte[].
        /// </summary>
        /// <param name="column"></param>
        /// <param name="WKB"></param>
        /// <param name="srid"></param>
        public void AddCoordinate(String column, Byte[] WKB, long srid) {
            long columnSRID = this.checkGeometryColumn(column);
            String wkbKey = "@wkb";
            NpgsqlCommand cmd = new NpgsqlCommand(String.Format("ST_GeomFromWKB({0}, {1})",wkbKey, srid));
            if(srid  != columnSRID) cmd.CommandText = String.Format("ST_Transform({0},{1})", cmd.CommandText, columnSRID);
        }

        /// <summary>
        /// Adds a coordinate by defining its x and y value.
        /// </summary>
        /// <param name="column"></param>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="srid"></param>
        public void AddCoordinate(String column, double x, double y, long srid) {
            CultureInfo culture = CultureInfo.GetCultureInfo("en-GB");
            if(!this.ti.ContainsKey(column)) {
                throw new Exception(
                String.Format("Column {0} does not exist in tableName {1}"
                , column, this.ti.SchemaTableName));
            }
            long columnSRID = checkGeometryColumn(column);
            string geometrySQL = String.Format(culture, "ST_SetSRID(ST_MakePoint({0},{1}),{2})", x, y, srid);
            if(srid != columnSRID) {
                geometrySQL = String.Format(culture, "ST_Transform({0},{1})", geometrySQL, columnSRID);
            }
            rowValue.Add(column, new NpgsqlCommand(geometrySQL));
            
        
        }

        public void Add(String column, Enum e) {
            throw new Exception("Can not insert enumerations");
        }

        public void Add(String column, DataGridViewCell cell) {
            this.Add(column, cell.Value);
        }
       

        public void Add(String column, ComboBox cb) {
            Object[] value = cb.SelectedValue as Object[];
            if(value != null) {
                this.Add(column, value[0]);
            } else {
                this.Add(column, cb.SelectedValue);
            }
        }


        public void Add(String column, TextBox tb) {
            this.Add(column, tb.Text);
        }

        /*
        public void AddID(String[] idColumns, Object[] idValues) { 
            if(idColumns.Count() != idValues.Count()) throw new Exception("number of id columns and id values differs.")
            for(int i=0; i < idColumns.Length; i++){
                this.Add(idColumns[i], idValues[i]);
            }
        }
        */
        public void AddID(String column, DataGridViewCell cell, bool useTag) {
            if(useTag) {
                Object tagvalue = cell.Tag;
                Object[] tagvalueArray = tagvalue as Object[];
                if(tagvalueArray != null) {
                    this.Add(column, tagvalueArray[0]);
                } else {
                    this.Add(column, tagvalue);
                }
            } else {
                this.Add(column, cell.Value);
            }
        }



        public void AddID(String column, Control control, bool useTag) {
            if(useTag) {
                Object tagvalue = control.Tag;
                Object[] tagvalueArray = tagvalue as Object[];
                if(tagvalueArray != null) {
                    this.Add(column, tagvalueArray[0]);
                } else {
                    this.Add(column, tagvalue);
                }
            } else {
                this.Add(column, control.Text);
            }
        }

        /*
        public void AddID(String column, TextBox tb, bool useTag) {
            if(useTag) {
                Object tagvalue = tb.Tag;
                Object[] tagvalueArray = tagvalue as Object[];
                if(tagvalueArray != null) {
                    this.Add(column, tagvalueArray[0]);
                } else { 
                    this.Add(column, tagvalue);
                }
            } else {
                this.Add(column, tb.Text);
            }
        }*/

        public void Add(String column, MonthCalendar mc) {
            this.Add(column, mc.SelectionStart);
        }
        
        public void Add(String column, DateTimePicker dtp) {
            this.Add(column, dtp.Value);
        }


        public void Add(List<String> columns, object[] values) {
            for(int i=0; i< columns.Count; i++) this.Add(columns[i], values[i]);
        }
        public void Add(String[] columns, object[] values){
            for(int i=0; i< columns.Length; i++) this.Add(columns[i], values[i]);
        }


       

        public void Add(String column, object value) { 
            if(!this.ti.ContainsKey(column)) {
                throw new Exception(
                String.Format("Column \"{0}\" does not exist in tableName \"{1}\""
                , column, this.ti.SchemaTableName)); 
            }
            if(value is Object[]) throw new Exception("Unboxing of Object[] not supported. Use a type specific method");
            bool toInsert = DataHelper.CanConvert(this.ti[column].ValueType, value);

            if(toInsert) rowValue.Add(column, value);
        }

        public void AddFile(FileInfo fileInfo, String MainBlobColumn, String FileNameColumn, String FileExtensionColumn, String FileTypeColumn, String FileType=null) {
            if(fileInfo == null || !fileInfo.Exists) return;
            Byte[] bytea = File.ReadAllBytes(fileInfo.FullName);
            if(!String.IsNullOrEmpty(MainBlobColumn))       this.Add(MainBlobColumn, bytea);
            if(!String.IsNullOrEmpty(FileNameColumn))       this.Add(FileNameColumn, fileInfo.Name);
            if(!String.IsNullOrEmpty(FileExtensionColumn))  this.Add(FileExtensionColumn, fileInfo.Extension.Remove(0, 1));

            if(!String.IsNullOrEmpty(FileTypeColumn)) {
                if(FileType == null) {
                    System.Drawing.Imaging.ImageFormat imgFormat = DataHelper.getImageFormat(bytea);
                    if(imgFormat != null) {
                        FileType = imgFormat.ToString();
                    }else if(SpectralTools.ESL_Reader.isValidFile(fileInfo.Name)){
                        FileType = "esl";
                    }else if(SpectralTools.ASD_Reader.isValidASDSpectrum(bytea)){
                        FileType = "asd";
                    }else{
                        FileType = fileInfo.Extension;
                    }
                }
                
                this.Add(FileTypeColumn, FileType);


            }

        } 
    }
}
